<?php
/**
 * ImportService.php
 *
 * @copyright   2023 thingsworld.com.cn - All Rights Reserved
 * @link        http://thingsworld.com.cn/
 * @author      stiffer.chen <chenl@thingsworld.com.cn>
 * @created     2023-04-08 00:43:16
 * @modified    2023-04-08 00:43:16
 *
 */

namespace App\Services;

use App\Library\Logger;
use App\Models\CityModel;
use App\Models\Customer;
use App\Models\Dept\Zone;
use App\Models\Product;
use App\Models\Project;
use App\Models\UserModel;
use App\Repositories\CustomerRepo;
use App\Repositories\Product\TypeRepo;
use App\Repositories\ProductRepo;
use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ImportService
{
    private $logger;

    public function __construct()
    {
        $this->logger = new Logger('import');
    }

    private function validateProduct()
    {
        $workSheet = $this->getWorksheet();
        $rowCount = $workSheet->getHighestRow();
        for ($i = 2; $i <= $rowCount; $i++) {
            $customerName = trim($workSheet->getCellByColumnAndRow(1, $i)->getValue() ?? '');
            $customer = Customer::with(['department'])->byName($customerName)->first();
            if (empty($customer)) {
                throw new \Exception("{$i} - {$customerName} 不存在，请检查");
            }
        }
    }

    public function projectProducts()
    {

        $loggger = new Logger("project_product");

        $projectId = request('project_id') ?? 0;
        $project = Project::find($projectId);
        if (empty($project) || !$project->mark) {
            throw new \Exception('项目不存在');
        }

        $customerId = $project->customer_id;
        $customer = Customer::find($customerId);

        $workSheet = $this->getWorksheet();
        $rowCount = $workSheet->getHighestRow();
        $successCount = 0;

        for ($i = 2; $i <= $rowCount; $i++) {
            $productName = trim($workSheet->getCellByColumnAndRow(1, $i)->getValue() ?? '');
            $productModel = ''; // trim($workSheet->getCellByColumnAndRow(1, $i)->getValue() ?? '');
            $product = Product::byName($productName)->first();
            if (empty($product)) {
                $productId = 0;
            } else {
                $productId = $product->id;
                if ($product->project_id && $project->mark) {
                    $tempProject = Project::find($product->project_id);
                    if (!empty($tempProject) && $tempProject->mark) {
                        $projectName = $tempProject->name ?? '';
                        $tempProjectId = $tempProject->id ?? 0;
                        throw new \Exception("设备已绑定项目(#{$productName}-{$productModel}-" . $tempProjectId . "-{$projectName})");
                    }
                }
            }
        }

        for ($i = 2; $i <= $rowCount; $i++) {
            $productName = trim($workSheet->getCellByColumnAndRow(1, $i)->getValue() ?? '');
            if (!$productName) {
                continue;
            }
            $productModel = ''; // trim($workSheet->getCellByColumnAndRow(1, $i)->getValue() ?? '');
            $product = Product::byName($productName)->first();
            if (empty($product)) {
                $productId = 0;
            } else {
                $productId = $product->id;
                if ($product->project_id && $project->mark) {
                    $tempProject = Project::find($product->project_id);
                    if (!empty($tempProject) && $tempProject->mark) {
                        $projectName = $tempProject->name ?? '';
                        $tempProjectId = $tempProject->id ?? 0;
                        $loggger->warning("设备已绑定项目(#{$productName}-{$productModel}-" . $tempProjectId . "-{$projectName})");
                        continue;
                        // throw new \Exception("设备已绑定项目(#{$productName}-{$productModel})");
                    }
                }
            }
            $productType = trim($workSheet->getCellByColumnAndRow(2, $i)->getValue() ?? '');
            $type = Product\Type::byName($productType)->first();
            if (empty($type)) {
                $type = new Product\Type([
                    'name' => $productType,
                    'intro' => ''
                ]);
                $type->saveOrFail();
            }
            $typeId = $type->id;
            $dateBuy = trim($workSheet->getCellByColumnAndRow(3, $i)->getValue() ?? '');
            try {
                $dateBuy = Carbon::parse($dateBuy)->toDateString();
            } catch (\Exception $e) {
                $dateBuy = Carbon::now()->toDateString();
            }
            $provinceName = trim($workSheet->getCellByColumnAndRow(4, $i)->getValue() ?? '');
            $provinceName = str_replace('市', '', str_replace('省', '', $provinceName));
            $province = CityModel::byName($provinceName)->byPid(0)->first();
            $provinceId = $province->id ?? 0;

            $cityName = trim($workSheet->getCellByColumnAndRow(5, $i)->getValue() ?? '');
            $cityName = str_replace('市', '', $cityName);
            $city = CityModel::byName($cityName)->byPid($provinceId)->first();
            $cityId = $city->id ?? 0;

            $countyName = trim($workSheet->getCellByColumnAndRow(6, $i)->getValue() ?? '');
            $countyName = str_replace('县', '', str_replace('区', '', $countyName));
            $county = CityModel::byName($countyName)->byPid($cityId)->first();
            $countyId = $county->id ?? 0;

            $address = trim($workSheet->getCellByColumnAndRow(7, $i)->getValue() ?? '');

            $warrantyStart = trim($workSheet->getCellByColumnAndRow(8, $i)->getValue() ?? '');
            try {
                $warrantyStart = Carbon::parse($warrantyStart)->toDateString();
            } catch (\Exception $e) {
                $warrantyStart = Carbon::now()->toDateString();
            }

            $warrantyEnd = trim($workSheet->getCellByColumnAndRow(9, $i)->getValue() ?? '');
            try {
                $warrantyEnd = Carbon::parse($warrantyEnd)->toDateString();
            } catch (\Exception $e) {
                $warrantyEnd = Carbon::now()->toDateString();
            }

            $productName = trim($productName);
            $data = [
                'customer_id' => $customerId,
                'type_id' => $typeId,
                'department_id' => $customer->department->id ?? 0,
                'product_number' => ProductRepo::generateProductNumber($productId),
                'name' => $productName,
                'model' => $productModel,
                'province_id' => $provinceId,
                'province_name' => $province->name ?? '',
                'city_id' => $cityId,
                'city_name' => $city->name ?? '',
                'county_id' => $countyId,
                'county_name' => $county->name ?? '',
                'address' => $address,
                'install_address' => $address,
                'warranty_start' => $warrantyStart,
                'warranty_end' => $warrantyEnd,
                'project_id' => $projectId
            ];
            if (!$productName) {
                continue;
            }
            if (empty($product)) {
                $product = new Product();
            }
            $product->fill($data);
            $product->saveOrFail();
            $successCount += 1;
        }
        return $successCount;
    }

    public function product()
    {
        $this->validateProduct();
        $workSheet = $this->getWorksheet();
        $rowCount = $workSheet->getHighestRow();
        $successCount = 0;
        $this->logger->info("导入设备数据 start...");
        for ($i = 2; $i <= $rowCount; $i++) {
            $customerName = trim($workSheet->getCellByColumnAndRow(1, $i)->getValue() ?? '');
            $customer = Customer::with(['department'])->byName($customerName)->first();
            $customerId = $customer->id ?? 0;
            $departmentId = $customer->department->id ?? 0;
            $productModel = trim($workSheet->getCellByColumnAndRow(2, $i)->getValue() ?? '');
            $productName = trim($workSheet->getCellByColumnAndRow(3, $i)->getValue() ?? '');
            $productTypeName = trim($workSheet->getCellByColumnAndRow(4, $i)->getValue() ?? '');
            $typeId = 0;
            if ($productTypeName) {
                $productType = Product\Type::byName($productTypeName)->first();
                if (empty($productType)) {
                    $productType = new Product\Type();
                    $productTypeData = [
                        'type_number' => TypeRepo::generateNumber($productType->id ?? 0),
                        'name' => $productTypeName,
                        'intro' => ''
                    ];
                    $productType->fill($productTypeData);
                    $productType->saveOrFail();
                }
                $typeId = $productType->id ?? 0;
            }

            $province = trim($workSheet->getCellByColumnAndRow(5, $i)->getValue() ?? '');
            $province = str_replace('市', '', str_replace('省', '', $province));
            $provinceModel = CityModel::byName($province)->byPid(0)->first();
            $provinceName = $provinceModel->name ?? '';
            $provinceId = $provinceModel->id ?? 0;

            $city = trim($workSheet->getCellByColumnAndRow(6, $i)->getValue() ?? '');
            $city = str_replace('市', '', $city);
            $cityModel = CityModel::byName($city)->byPid($provinceId)->first();
            $cityName = $cityModel->name ?? '';
            $cityId = $cityModel->id ?? 0;

            $county = trim($workSheet->getCellByColumnAndRow(7, $i)->getValue() ?? '');
            $county = str_replace('市', '', str_replace('县', '', str_replace('区', '', $county)));
            $countyModel = CityModel::byName($county)->byPid($cityId)->first();
            $countyName = $countyModel->name ?? '';
            $countyId = $countyModel->id ?? 0;

            $address = trim($workSheet->getCellByColumnAndRow(8, $i)->getValue() ?? '');

            try {
                $warrantyStart = trim($workSheet->getCellByColumnAndRow(9, $i)->getValue() ?? '');
                $warrantyStart = Carbon::parse(\PHPExcel_Shared_Date::ExcelToPHP($warrantyStart))->toDateString();
            } catch (\Exception $e) {
                $warrantyStart = trim($workSheet->getCellByColumnAndRow(9, $i)->getValue() ?? '');
                try {
                    $warrantyStart = Carbon::parse($warrantyStart)->toDateString();
                } catch (\Exception $e) {
                    $warrantyStart = '';
                }
            }

            try {
                $warrantyEnd = trim($workSheet->getCellByColumnAndRow(10, $i)->getValue() ?? '');
                $warrantyEnd = Carbon::parse(\PHPExcel_Shared_Date::ExcelToPHP($warrantyEnd))->toDateString();
            } catch (\Exception $e) {
                $warrantyEnd = trim($workSheet->getCellByColumnAndRow(10, $i)->getValue() ?? '');
                try {
                    $warrantyEnd = Carbon::parse($warrantyEnd)->toDateString();
                } catch (\Exception $e) {
                    $warrantyEnd = '';
                }
            }
            // 产品名称: 出厂编号，具有唯一性
            $product = Product::byName($productName)->first();
            if (empty($product)) {
                $product = new Product();
            }
            $productName = trim($productName);
            if (!$productName) {
                continue;
            }

            $productData = [
                'customer_id' => $customerId,
                'type_id' => $typeId,
                'department_id' => $departmentId,
                'product_number' => ProductRepo::generateProductNumber($product->id ?? 0),
                'name' => $productName,
                'model' => $productModel,
                'province_id' => $provinceId,
                'province_name' => $provinceName,
                'city_id' => $cityId,
                'city_name' => $cityName,
                'county_id' => $countyId,
                'county_name' => $countyName,
                'address' => $address,
                'install_address' => $address,
                'warranty_start' => $warrantyStart,
                'warranty_end' => $warrantyEnd,
                'create_user_id' => registry('user_id') ?? 0
            ];
            $product->fill($productData);
            $product->saveOrFail();
            $successCount += 1;
        }
        $this->logger->info("导入设备数据, 导入量: {$successCount}, complete...");
        return $successCount;
    }

    public function customer()
    {
        $workSheet = $this->getWorksheet();
        $rowCount = $workSheet->getHighestRow();

        $successCount = 0;
        $this->logger->info("导入客户数据 start...");
        for ($i = 2; $i <= $rowCount; $i++) {
            $customerName = trim($workSheet->getCellByColumnAndRow(1, $i)->getValue() ?? '');
            $customerShortName = trim($workSheet->getCellByColumnAndRow(2, $i)->getValue() ?? '');

            // 省份: 去掉省、市字符
            $province = trim($workSheet->getCellByColumnAndRow(6, $i)->getValue() ?? '');
            $province = str_replace('市', '', str_replace('省', '', $province));
            $provinceModel = CityModel::byName($province)->byPid(0)->first();
            if (empty($provinceModel)) {
                $this->logger->info("数据异常, {$customerName}的 省份: {$province} 在数据库中不存在");
            }
            $provinceName = $provinceModel->name ?? '';
            $provinceId = $provinceModel->id ?? 0;

            $city = trim($workSheet->getCellByColumnAndRow(7, $i)->getValue() ?? '');
            $city = str_replace('市', '', $city);
            $cityModel = CityModel::byName($city)->byPid($provinceId)->first();
            if (empty($cityModel)) {
                $this->logger->info("数据异常, {$customerName}的 省份: {$province} 下的城市: {$city} 在数据库中不存在");
            }
            $cityName = $cityModel->name ?? '';
            $cityId = $cityModel->id ?? 0;

            $county = trim($workSheet->getCellByColumnAndRow(8, $i)->getValue() ?? '');
            $county = str_replace('市', '', str_replace('县', '', str_replace('区', '', $county)));
            $countyModel = CityModel::byName($county)->byPid($cityId)->first();
            if (empty($cityModel)) {
                $this->logger->info("数据异常, {$customerName}的 省份: {$province} 下的城市: {$city} 下的区县: {$county} 在数据库中不存在");
            }
            $countyName = $countyModel->name ?? '';
            $countyId = $countyModel->id ?? 0;

            $address = trim($workSheet->getCellByColumnAndRow(9, $i)->getValue() ?? '');
            $industryName = trim($workSheet->getCellByColumnAndRow(10, $i)->getValue() ?? '');

            $customerUserName = trim($workSheet->getCellByColumnAndRow(3, $i)->getValue() ?? '');
            $phone = trim($workSheet->getCellByColumnAndRow(4, $i)->getValue() ?? '');
            $email = trim($workSheet->getCellByColumnAndRow(5, $i)->getValue() ?? '');

            // 签约日期
            try {
                $contactAt = trim($workSheet->getCellByColumnAndRow(11, $i)->getValue() ?? '');
                $contactAt = Carbon::parse(\PHPExcel_Shared_Date::ExcelToPHP($contactAt))->toDateString();
            } catch (\Exception $e) {
                $contactAt = trim($workSheet->getCellByColumnAndRow(11, $i)->getValue() ?? '');
                try {
                    $contactAt = Carbon::parse($contactAt)->toDateString();
                } catch (\Exception $e) {
                    $contactAt = '';
                    $this->logger->info("{$customerName}的 签约日期: {$contactAt} 异常");
                }
            }

            $serviceLimit = 0;
            if ($contactAt) {
                $serviceLimit = Carbon::now()->diffInYears(Carbon::parse($contactAt), true);
            }
            $customer = Customer::byName($customerName)->first();
//            if (empty($customer)) {
//                $customer = Customer::byShortName($customerShortName)->first();
//            }

            if (empty($customer)) {
                $customer = new Customer();
            }

            // 服务部门
            $deptZone = Zone::with(['dept'])->byProvinceId($provinceId)->first();
            $departmentId = $deptZone->dept_id ?? 0;
            $departmentName = $deptZone->dept->name ?? '';

            $userName = trim($workSheet->getCellByColumnAndRow(12, $i)->getValue() ?? '');
            if (!$userName) {
                $ownerId = 0;
                $ownerName = '';
                $this->logger->info("数据异常: {$customerName} 的负责人姓名为空");
            } else {
                $user = UserModel::byRealname($userName)->first();
                if (empty($user)) {
                    $user = new UserModel();
                    $user->realname = $userName;
                    $user->username = $userName;
                    $user->saveOrFail();
                }
                $ownerId = $user->id ?? 0;
                $ownerName = $user->realname ?? '';
            }

            $customerData = [
                'customer_number' => CustomerRepo::generateCustomerNumber($customer->id ?? 0),
                'name' => $customerName,
                'short_name' => $customerShortName,
                'department_id' => $departmentId,
                'department_name' => $departmentName,
                'province_id' => $provinceId,
                'province_name' => $provinceName,
                'city_id' => $cityId,
                'city_name' => $cityName,
                'county_id' => $countyId,
                'county_name' => $countyName,
                'address' => $address,
                'status' => 1,
                'industry_name' => $industryName,
                'contact_at' => $contactAt,
                'owner_id' => $ownerId,
                'owner_name' => $ownerName,
                'service_limit' => $serviceLimit,
                'create_user_id' => registry('user_id') ?? 0
            ];
            $this->logger->info("客户{$i} 名称: {$customerName}");
            $customer->fill($customerData);
            $customer->saveOrFail();

            $customerUser = Customer\User::byName($customerUserName)->first();
            if (empty($customerUser)) {
                $customerUser = Customer\User::byPhone($phone)->first();
            }
            if (empty($customerUser)) {
                $customerUser = new Customer\User();
            }

            $customerUserData = [
                'name' => $customerUserName,
                'phone' => $phone,
                'office_phone' => $phone,
                'email' => $email,
                'customer_id' => $customer->id ?? 0,
                'customer_name' => $customer->name ?? '',
                'province_id' => $provinceId,
                'province_name' => $provinceName,
                'city_id' => $cityId,
                'city_name' => $cityName,
                'county_id' => $countyId,
                'county_name' => $countyName,
                'address' => $address,
                'status' => 1,
                'create_user_id' => registry('user_id')
            ];
            $customerUser->fill($customerUserData);
            $customerUser->saveOrFail();

            $customer->customer_user_id = $customerUser->id ?? 0;
            $customer->saveOrFail();
            // $this->logger->info("导入客户数据, 导入量: {$successCount}, complete...");
            $this->logger->info("客户{$i} id: {$customer->id}, 名称: {$customer->name}, 联系人: $customerUser->name");
            $successCount += 1;
        }
        return $successCount;
    }

    private function getWorksheet(): Worksheet
    {
        $file = request()->file('attachment');
        if (empty($file)) {
            throw new \Exception('请上传数据表格');
        }
        $fileOriginName = $file->getClientOriginalName();
        // 文件验证
        $suffix = $this->getSuffix($fileOriginName);
        if (!in_array($suffix, $this->types())) {
            throw new \Exception('文件类型错误, 仅支持excel的xlsx格式的表格文件');
        }
        // 文件mime验证
        $fileMimeType = $file->getMimeType() ?? '';
        $allowMimeTypes = $this->mimeTypes();
        $currentMimeType = $allowMimeTypes[$suffix] ?? '';
        if (!$currentMimeType || $currentMimeType != $fileMimeType) {
            throw new \Exception('文件类型异常(mime)');
        }
        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($file);
        $worksheet = $spreadsheet->getActiveSheet();
        // 行数: 第一行为标题
        $rowCount = $worksheet->getHighestRow();
        if ($rowCount <= 1) {
            throw new \Exception('该excel表格无数据内容');
        }
        return $worksheet;
    }

    /**
     * 获取文件后缀
     *
     * @param string $fileName
     * @return string
     */
    private function getSuffix(string $fileName): string
    {
        $fileName = trim($fileName);
        if (empty($fileName)) {
            return '';
        }
        $fileNameArr = explode('.', $fileName);
        if (count($fileNameArr) == 1) {
            return '';
        }
        return strtolower($fileNameArr[count($fileNameArr) - 1]);
    }

    /**
     * 允许上传的 mime 类型
     *
     * @return string[]
     */
    private function mimeTypes()
    {
        return [
            // 'xls' => 'application/vnd.ms-excel',
            'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        ];
    }

    /**
     * 允许上传的文件后缀
     *
     * @return string[]
     */
    private function types()
    {
        return [
            'xls',
            'xlsx'
        ];
    }
}